package com.ys.controller.api.base;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ys.config.MenuId;
import com.ys.entity.base.TbUserPayApply;
import com.ys.entity.base.TbUserPayApplyItem;
import com.ys.entity.sys.YsCol;
import com.ys.enume.sys.YsColEnum;
import com.ys.service.base.TbUserPayApplyService;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.UploadService;
import com.ys.util.ExportExcelUtils;
import com.ys.util.admin.ConditionUtil;
import com.ys.util.shiro.ShiroUtils;
import com.ys.vo.LayuiTableVoJA;
import com.ys.vo.R;

/**
 * 员工工资申请单
 */
@Controller
@RequestMapping("/api/inter/tbUserPayApply")
public class ApiInterTbUserPayApplyController {
    private static final Logger logger = LoggerFactory.getLogger(ApiInterTbUserPayApplyController.class);

    @Autowired
    private CommonApiService<TbUserPayApply> commonApiService;

    @Autowired
    private TbUserPayApplyService tbUserPayApplyService;

    @RequiresPermissions("tbUserPayApply:view")
    @RequestMapping("/search")
    @ResponseBody
    public LayuiTableVoJA search(@ModelAttribute TbUserPayApply cond,String conditions){
        cond.setOrderby("A.createTime DESC")
        .addLeftJoin("YS_ProcInst C ON  C.Bid = A.Bid")
        .addField("C.UpdateTime updateTime1")
        .addLeftJoin("TS_UserChange t2 on  A.CreateChangeId = t2.id")
        .addField("CONCAT(t2.UserId,'/', t2.UserName) as createUserInfo")
         .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
         .addField("oi.OrgName AS orgName");
        return commonApiService.searchLay(cond);
    }

    /**
     * 新增
     */
    @RequiresPermissions("tbUserPayApply:add")
    @RequestMapping("/toAdd")
    @ResponseBody
    public R toAdd(HttpServletRequest request, @RequestParam Map<String, Object> params,String subData
    ) {
        try {
            logger.info("员工工资申请单新增传参：" + params);
            //修改
            return tbUserPayApplyService.add(params,subData);
        } catch (Exception e) {
            logger.error("员工工资申请单新增出错：", e.getMessage(), e);
            return R.error("系统正在开小差，请联系客服，我们将第一时间为您解决！");
        }
    }


    /**
     * 修改
     */
    @RequiresPermissions("tbUserPayApply:add")
    @RequestMapping("/toEdit")
    @ResponseBody
    public R toEdit(HttpServletRequest request, @RequestParam Map<String, Object> params,String subData
    ) {
        try {
            logger.info("员工工资申请单修改传参：" + params);
            //修改
            return tbUserPayApplyService.edit(params,subData);
        } catch (Exception e) {
            logger.error("员工工资申请单修改出错：", e.getMessage(), e);
            return R.error("系统正在开小差，请联系客服，我们将第一时间为您解决！");
        }
    }

    @RequestMapping("/export")
    @ResponseBody
    public R export(@ModelAttribute TbUserPayApply cond, String conditions) throws Exception{

        cond.setPage(0).setSize(999999);
        cond.setOrderby("A.createTime DESC")
                .addLeftJoin("YS_ProcInst C ON  C.Bid = A.Bid")
                .addField("C.UpdateTime updateTime1")
                .addLeftJoin("TS_UserChange t2 on  A.CreateChangeId = t2.id")
                .addField("CONCAT(t2.UserId,'/', t2.UserName) as createUserInfo")
                .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
                .addField("oi.OrgName AS orgName");

        List<Map<String, Object>> listData = cond.queryMap();


        List<YsCol> listField = new YsCol()
                .setMenuId(MenuId.TB_UserPayApply)
                .setFrontShow(YsColEnum.FrontShow.SHOW.value)
                .setOrderby("A.SortNo asc")
                .queryList();

        ExportExcelUtils<TbUserPayApply> util = new ExportExcelUtils<TbUserPayApply>(new TbUserPayApply());

        String toFilePath = util.exportExcel(listData, listField, "员工工资申请单表");
        UploadService.UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
        return R.ok(dto.getUrl());
    }


    /**
     * 删除员工工资申请
     */
    @PostMapping("/toDel")
    @ResponseBody
    public R toDel(Long id){
        try {
            return tbUserPayApplyService.delete(id);
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("操作失败，请联系客服");
        }
    }

    @PostMapping("/flowPass")
    @ResponseBody
    public R flowPass(HttpServletRequest request, Long id){
        try {
            return tbUserPayApplyService.flowPass(id, ShiroUtils.getUserId());
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("服务器正在开小差，请联系客服");
        }
    }

    /**
     * 流转退回
     */
    @PostMapping("/flowRefuse")
    @ResponseBody
    public R flowRefuse(HttpServletRequest request, Long id, String reason){

        try {
            return tbUserPayApplyService.flowRefuse(id, reason, ShiroUtils.getUserId());
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("服务器正在开小差，请联系客服");
        }
    }

    /**
     * 流转驳回
     */
    @PostMapping("/flowInit")
    @ResponseBody
    public R flowInit(HttpServletRequest request, Long id, String reason){

        try {
            return tbUserPayApplyService.flowInit(id, reason, ShiroUtils.getUserId());
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("服务器正在开小差，请联系客服");
        }
    }

    /**
     *工资付款调用
     * @param cond
     * @param conditions
     * @return
     */
    @RequestMapping("/searchGz")
    @ResponseBody
    public R searchGz(@ModelAttribute TbUserPayApply cond,String conditions){
        String orgName = ConditionUtil.getConditionValue(conditions, "orgName");
        String bid = ConditionUtil.getConditionValue(conditions, "bid");

        String sql = "";
        if (orgName != null && orgName.length() != 0) {
            sql = sql + (" AND oi.OrgName LIKE '%" + orgName + "%'");
        }
        if (bid != null && bid.length() != 0) {
            sql = sql + (" AND A.bid LIKE '%" + bid + "%'");
        }
        List<Map<String ,Object>> tbUserPayApplies = new TbUserPayApply()
                .setEffectStock(1)
                .where("(SELECT count(1) FROM TB_PayrollSlip WHERE UserPayApplyId = A.Id) = 0" + sql)
                .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
                .addField("oi.OrgName AS orgName")
                .setOrderby("A.createTime DESC").queryMap();
        return  R.ok("成功",tbUserPayApplies);
    }

    /**
     * 工资付款详情调用
     * @param pid
     * @return
     */
    @RequestMapping("/searchGzXq")
    @ResponseBody
    public R searchGzXq(Long pid){
        List<Map<String, Object>> sonlist = new TbUserPayApplyItem()
                .setPid(pid)
                .addLeftJoin("TB_UserPaySlip R ON R.id = A.UserPaySlipidid")//员工工资表
                .addField("R.pointMonth AS pointMonth")//发薪月份
                .addField("SUBSTRING(CONVERT(varchar, DATEADD(MONTH,1,R.pointMonth + '-01'), 23),0,8) AS payDate")//发薪年月
                .addField("R.wagesPay AS wagesPay")//应发工资
                .addField("R.needPaySlip AS needPaySlip")//应付工资
                .addField("R.personalTax AS personalTax")//个人所得税
                .addField("R.realityPaySlip AS realityPaySlip")//实发工资

                .addLeftJoin("TS_UserInfo U ON U.UserId = R.UserId")//用户信息表
                .addField("(U.UserId + '/' + U.UserName) AS userName")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo oi ON oi.OrgId = U.DeptId")//组织机构表
                .addField("oi.DeptName AS deptName")//部门

                .addLeftJoin("THR_UserArchives UAS ON UAS.UserId = R.UserId")//用户档案表
                .addField("UAS.UserState AS userState")//员工状态

                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = R.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                 .addLeftJoin("TS_OrganizationAccount oa ON oa.OrgId = U.DeptId")//部门绑定账号
                .addLeftJoin("THR_OperateCompanyApply THRO ON  THRO.Vgsbm = oa.Vgsbm")
                .addField("oa.Vgsbm AS vgsbm,THRO.vgsjc AS vgsjc")//运营公司

                .addLeftJoin("TS_UserBankInfo ubi ON  ubi.UserId = U.UserId")
                .addField("ubi.vyhzh AS ygvyhzh,ubi.vkhh AS ygvkhh,ubi.vyhzhmc AS ygvyhzhmc")//收款信息

                .addLeftJoin("TB_UserMonthPerformance MP ON MP.Id = R.MonthPerformanceId")//员工月度绩效信息表
                .addField("MP.Rank AS rank")//岗位职级
                .addField("MP.Type AS type")//岗位工种(0总经理 1副总经理 2责任月薪型员工 3绩效提奖型员工 4研发人员)

                .addLeftJoin("TB_JobLevel JB ON JB.Id = R.RoleLevelId")//岗位职级管理表
                .addField("JB.Category AS category")//岗位类别
                .addField("JB.Categories AS categories")//职级大类(0A 1B 2C 3D 4P)
                .addField("A.id AS userPayApplyItemId")//id
//                .where("R.realityPaySlip != 0")//不能为0
                .queryMap();
        return  R.ok("成功",sonlist);
    }


    /**
     * 医保付款调用
     * @param cond
     * @param conditions
     * @return
     */
    @RequestMapping("/searchYb")
    @ResponseBody
    public R searchYb(@ModelAttribute TbUserPayApply cond,String conditions){
        String orgName = ConditionUtil.getConditionValue(conditions, "orgName");
        String bid = ConditionUtil.getConditionValue(conditions, "bid");

        String sql = "";
        if (orgName != null && orgName.length() != 0) {
            sql = sql + (" AND oi.OrgName LIKE '%" + orgName + "%'");
        }
        if (bid != null && bid.length() != 0) {
            sql = sql + (" AND A.bid LIKE '%" + bid + "%'");
        }
        List<Map<String ,Object>> tbUserPayApplies = new TbUserPayApply()
                .setEffectStock(1)
                .where("(SELECT count(1) FROM TB_MedicarePay WHERE UserPayApplyId = A.Id) = 0" + sql)
                .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
                .addField("oi.OrgName AS orgName")
                .setOrderby("A.createTime DESC").queryMap();
        return  R.ok("成功",tbUserPayApplies);
    }


    /**
     * 医保付款详情调用
     * @param pid
     * @return
     */
    @RequestMapping("/searchYbXq")
    @ResponseBody
    public R searchYbXq(Long pid){
        List<Map<String, Object>> sonlist = new TbUserPayApplyItem()
                .setPid(pid)
                .addLeftJoin("TB_UserPaySlip R ON R.id = A.UserPaySlipidid")//员工工资表
                .addField("R.pointMonth AS pointMonth")//发薪月份
                .addField("SUBSTRING(CONVERT(varchar, DATEADD(MONTH,1,R.pointMonth + '-01'), 23),0,8) AS payDate")//缴费年月
                .addField("R.healthCompany AS healthCompany")//医疗保险（单位）
                .addField("R.healthPersonal AS healthPersonal")//医疗保险（个人）
                .addField("R.birthCompany AS birthCompany")//生育保险（单位）

                .addLeftJoin("TS_UserInfo U ON U.UserId = R.UserId")//用户信息表
                .addField("(U.UserId + '/' + U.UserName) AS userName")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo oi ON oi.OrgId = U.DeptId")//组织机构表
                .addField("oi.OrgName AS deptName")//部门

                .addLeftJoin("THR_UserArchives UAS ON UAS.UserId = R.UserId")//用户档案表
                .addField("UAS.UserState AS userState")//员工状态

                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = R.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                .addLeftJoin("TS_OrganizationAccount oa ON oa.OrgId = U.DeptId")//部门绑定账号
                .addLeftJoin("THR_OperateCompanyApply THRO ON  THRO.Vgsbm = oa.Vgsbm")
                .addField("oa.Vgsbm AS vgsbm,THRO.vgsjc AS vgsjc")//运营公司

                .addLeftJoin("TS_UserBankInfo ubi ON  ubi.UserId = U.UserId")
                .addField("ubi.vyhzh AS ygvyhzh,ubi.vkhh AS ygvkhh,ubi.vyhzhmc AS ygvyhzhmc")//收款信息

                .addLeftJoin("TB_UserMonthPerformance MP ON MP.Id = R.MonthPerformanceId")//员工月度绩效信息表
                .addField("MP.Rank AS rank")//岗位职级
                .addField("MP.Type AS type")//岗位工种(0总经理 1副总经理 2责任月薪型员工 3绩效提奖型员工 4研发人员)

                .addLeftJoin("TB_JobLevel JB ON JB.Id = R.RoleLevelId")//岗位职级管理表
                .addField("JB.Category AS category")//岗位类别
                .addField("JB.Categories AS categories")//职级大类(0A 1B 2C 3D 4P)
                .addField("A.id AS userPayApplyItemId")//id
                .where("R.healthCompany != 0 OR R.healthPersonal != 0 OR R.birthCompany != 0")//不能为0
                .queryMap();
        return  R.ok("成功",sonlist);
    }


    /**
     * 社保付款调用
     * @param cond
     * @param conditions
     * @return
     */
    @RequestMapping("/searchSb")
    @ResponseBody
    public R searchSb(@ModelAttribute TbUserPayApply cond,String conditions){
        String orgName = ConditionUtil.getConditionValue(conditions, "orgName");
        String bid = ConditionUtil.getConditionValue(conditions, "bid");

        String sql = "";
        if (orgName != null && orgName.length() != 0) {
            sql = sql + (" AND oi.OrgName LIKE '%" + orgName + "%'");
        }
        if (bid != null && bid.length() != 0) {
            sql = sql + (" AND A.bid LIKE '%" + bid + "%'");
        }
        List<Map<String ,Object>> tbUserPayApplies = new TbUserPayApply()
                .setEffectStock(1)
                .where("(SELECT count(1) FROM TB_SocialSecurityPay WHERE UserPayApplyId = A.Id) = 0" + sql)
                .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
                .addField("oi.OrgName AS orgName")
                .setOrderby("A.createTime DESC").queryMap();
        return  R.ok("成功",tbUserPayApplies);
    }


    /**
     * 社保付款详情调用
     * @param pid
     * @return
     */
    @RequestMapping("/searchSbXq")
    @ResponseBody
    public R searchSbXq(Long pid){
        List<Map<String, Object>> sonlist = new TbUserPayApplyItem()
                .setPid(pid)
                .addLeftJoin("TB_UserPaySlip R ON R.id = A.UserPaySlipidid")//员工工资表
                .addField("R.pointMonth AS pointMonth")//发薪月份
                .addField("SUBSTRING(CONVERT(varchar, DATEADD(MONTH,1,R.pointMonth + '-01'), 23),0,8) AS payDate")//缴费年月
                .addField("R.retirementCompany AS retirementCompany")//养老保险（单位）
                .addField("R.retirementPersonal AS retirementPersonal")//养老保险（个人）
                .addField("R.loseWorkCompany AS loseWorkCompany")//失业保险（单位）
                .addField("R.loseWorkPersonal AS loseWorkPersonal")//失业保险（个人）
                .addField("R.injuryCompany AS injuryCompany")//工伤保险（单位）

                .addLeftJoin("TB_SocialSecurity ss ON ss.id = R.SocialSecurityId")//社保管理
                .addField("ss.SoseBase AS soseBase")//社保基数

                 .addLeftJoin("TS_UserInfo U ON U.UserId = R.UserId")//用户信息表
                .addField("(U.UserId + '/' + U.UserName) AS userName")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo oi ON oi.OrgId = U.DeptId")//组织机构表
                .addField("oi.OrgName AS deptName")//部门

                .addLeftJoin("THR_UserArchives UAS ON UAS.UserId = R.UserId")//用户档案表
                .addField("UAS.UserState AS userState")//员工状态

                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = R.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                .addLeftJoin("TS_OrganizationAccount oa ON oa.OrgId = U.DeptId")//部门绑定账号
                .addLeftJoin("THR_OperateCompanyApply THRO ON  THRO.Vgsbm = oa.Vgsbm")
                .addField("oa.Vgsbm AS vgsbm,THRO.vgsjc AS vgsjc")//运营公司

                .addLeftJoin("TS_UserBankInfo ubi ON  ubi.UserId = U.UserId")
                .addField("ubi.vyhzh AS ygvyhzh,ubi.vkhh AS ygvkhh,ubi.vyhzhmc AS ygvyhzhmc")//收款信息

                .addLeftJoin("TB_UserMonthPerformance MP ON MP.Id = R.MonthPerformanceId")//员工月度绩效信息表
                .addField("MP.Rank AS rank")//岗位职级
                .addField("MP.Type AS type")//岗位工种(0总经理 1副总经理 2责任月薪型员工 3绩效提奖型员工 4研发人员)

                .addLeftJoin("TB_JobLevel JB ON JB.Id = R.RoleLevelId")//岗位职级管理表
                .addField("JB.Category AS category")//岗位类别
                .addField("JB.Categories AS categories")//职级大类(0A 1B 2C 3D 4P)
                .addField("A.id AS userPayApplyItemId")//id
                .where("R.retirementCompany != 0 OR R.retirementPersonal != 0 OR R.loseWorkCompany != 0 OR R.loseWorkPersonal != 0 OR R.injuryCompany != 0 ")//不能为0
                .queryMap();
        return  R.ok("成功",sonlist);
    }

    /**
     * 公积金付款调用
     * @param cond
     * @param conditions
     * @return
     */
    @RequestMapping("/searchGjj")
    @ResponseBody
    public R searchGjj(@ModelAttribute TbUserPayApply cond,String conditions){
        String orgName = ConditionUtil.getConditionValue(conditions, "orgName");
        String bid = ConditionUtil.getConditionValue(conditions, "bid");

        String sql = "";
        if (orgName != null && orgName.length() != 0) {
            sql = sql + (" AND oi.OrgName LIKE '%" + orgName + "%'");
        }
        if (bid != null && bid.length() != 0) {
            sql = sql + (" AND A.bid LIKE '%" + bid + "%'");
        }
        List<Map<String ,Object>> tbUserPayApplies = new TbUserPayApply()
                .setEffectStock(1)
                .where("(SELECT count(1) FROM TB_ReservedPay WHERE UserPayApplyId = A.Id) = 0" + sql)
                .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
                .addField("oi.OrgName AS orgName")
                .setOrderby("A.createTime DESC").queryMap();
        return  R.ok("成功",tbUserPayApplies);
    }


    /**
     * 公积金付款详情调用
     * @param pid
     * @return
     */
    @RequestMapping("/searchGjjXq")
    @ResponseBody
    public R searchGjjXq(Long pid){
        List<Map<String, Object>> sonlist = new TbUserPayApplyItem()
                .setPid(pid)
                .addLeftJoin("TB_UserPaySlip R ON R.id = A.UserPaySlipidid")//员工工资表
                .addField("R.pointMonth AS pointMonth")//发薪月份
                .addField("SUBSTRING(CONVERT(varchar, DATEADD(MONTH,1,R.pointMonth + '-01'), 23),0,8) AS payDate")//缴费年月
                .addField("R.accumulationFundCompany AS accumulationFundCompany")//公积金（单位）
                .addField("R.accumulationFundPersonal AS accumulationFundPersonal")//公积金（个人）

                .addLeftJoin("TB_ProvidentFund pf ON pf.id = R.AccumulationFundId")//公积金管理
                .addField("pf.HousingPfBase AS housingPfBase")//公积金基数

                .addLeftJoin("TS_UserInfo U ON U.UserId = R.UserId")//用户信息表
                .addField("(U.UserId + '/' + U.UserName) AS userName")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo oi ON oi.OrgId = U.DeptId")//组织机构表
                .addField("oi.OrgName AS deptName")//部门

                .addLeftJoin("THR_UserArchives UAS ON UAS.UserId = R.UserId")//用户档案表
                .addField("UAS.UserState AS userState")//员工状态

                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = R.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                .addLeftJoin("TS_OrganizationAccount oa ON oa.OrgId = U.DeptId")//部门绑定账号
                .addLeftJoin("THR_OperateCompanyApply THRO ON  THRO.Vgsbm = oa.Vgsbm")
                .addField("oa.Vgsbm AS vgsbm,THRO.vgsjc AS vgsjc")//运营公司

                .addLeftJoin("TB_UserMonthPerformance MP ON MP.Id = R.MonthPerformanceId")//员工月度绩效信息表
                .addField("MP.Rank AS rank")//岗位职级
                .addField("MP.Type AS type")//岗位工种(0总经理 1副总经理 2责任月薪型员工 3绩效提奖型员工 4研发人员)

                .addLeftJoin("TB_JobLevel JB ON JB.Id = R.RoleLevelId")//岗位职级管理表
                .addField("JB.Category AS category")//岗位类别
                .addField("JB.Categories AS categories")//职级大类(0A 1B 2C 3D 4P)
                .addField("A.id AS userPayApplyItemId")//id
                .where("R.accumulationFundCompany != 0 OR R.accumulationFundPersonal != 0")//不能为0
                .queryMap();
        return  R.ok("成功",sonlist);
    }

    /**
     * 个税付款调用
     * @param cond
     * @param conditions
     * @return
     */
    @RequestMapping("/searchGs")
    @ResponseBody
    public R searchGs(@ModelAttribute TbUserPayApply cond,String conditions){
        String orgName = ConditionUtil.getConditionValue(conditions, "orgName");
        String bid = ConditionUtil.getConditionValue(conditions, "bid");

        String sql = "";
        if (orgName != null && orgName.length() != 0) {
            sql = sql + (" AND oi.OrgName LIKE '%" + orgName + "%'");
        }
        if (bid != null && bid.length() != 0) {
            sql = sql + (" AND A.bid LIKE '%" + bid + "%'");
        }
        List<Map<String ,Object>> tbUserPayApplies = new TbUserPayApply()
                .setEffectStock(1)
                .setStatus(0)
                .where("(SELECT count(1) FROM TB_IncomePay WHERE UserPayApplyId = A.Id) = 0" + sql)
                .addLeftJoin("TS_OrganizationInfo oi ON oi.Orgid = A.Orgid")//所属公司
                .addField("oi.OrgName AS orgName")
                .setOrderby("A.createTime DESC").queryMap();
        return  R.ok("成功",tbUserPayApplies);
    }


    /**
     * 个税付款详情调用
     * @param pid
     * @return
     */
    @RequestMapping("/searchGsXq")
    @ResponseBody
    public R searchGsXq(Long pid){
        List<Map<String, Object>> sonlist = new TbUserPayApplyItem()
                .setPid(pid)
                .addLeftJoin("TB_UserPaySlip R ON R.id = A.UserPaySlipidid")//员工工资表
                .addField("R.pointMonth AS pointMonth")//发薪月份
                .addField("SUBSTRING(CONVERT(varchar, DATEADD(MONTH,2,R.pointMonth + '-01'), 23),0,8) AS payDate")//缴费年月
                .addField("R.PersonalTax AS personalTax")//个人所得税

                .addLeftJoin("TS_UserInfo U ON U.UserId = R.UserId")//用户信息表
                .addField("(U.UserId + '/' + U.UserName) AS userName")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo oi ON oi.OrgId = U.DeptId")//组织机构表
                .addField("oi.OrgName AS deptName")//部门

                .addLeftJoin("THR_UserArchives UAS ON UAS.UserId = R.UserId")//用户档案表
                .addField("UAS.UserState AS userState")//员工状态

                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = R.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                .addLeftJoin("TS_OrganizationAccount oa ON oa.OrgId = U.DeptId")//部门绑定账号
                .addLeftJoin("THR_OperateCompanyApply THRO ON  THRO.Vgsbm = oa.Vgsbm")
                .addField("oa.Vgsbm AS vgsbm,THRO.vgsjc AS vgsjc")//运营公司

                .addLeftJoin("TS_UserBankInfo ubi ON  ubi.UserId = U.UserId")
                .addField("ubi.vyhzh AS ygvyhzh,ubi.vkhh AS ygvkhh,ubi.vyhzhmc AS ygvyhzhmc")//收款信息

                .addLeftJoin("TB_UserMonthPerformance MP ON MP.Id = R.MonthPerformanceId")//员工月度绩效信息表
                .addField("MP.Rank AS rank")//岗位职级
                .addField("MP.Type AS type")//岗位工种(0总经理 1副总经理 2责任月薪型员工 3绩效提奖型员工 4研发人员)

                .addLeftJoin("TB_JobLevel JB ON JB.Id = R.RoleLevelId")//岗位职级管理表
                .addField("JB.Category AS category")//岗位类别
                .addField("JB.Categories AS categories")//职级大类(0A 1B 2C 3D 4P)
                .addField("A.id AS userPayApplyItemId")//id
                .addField("(U.UserName + SUBSTRING(CONVERT(varchar, DATEADD(MONTH,1,R.pointMonth + '-01'), 23),0,8) + '个税') AS vbzXq")//详情备注
                .where("R.personalTax != 0 ")//不能为0
                .queryMap();
        return  R.ok("成功",sonlist);
    }

}
